/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cz.tpj.database.akcia;

import cz.tpj.database.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import cz.tpj.database.obrazky.Obrazok;
import cz.tpj.util.ErrorDatabase;
import java.util.ArrayList;


/**
 * Trieda sprostredkuvava metody pre pridavanie, odoberanie, editovanie a vyberanie
 * Akcii z databazy
 * @author jabo
 */
public class Table_Akcia {
    
    public static final String TABLE       = "akcia";
    public static final String ID          = "id";
    public static final String NAME        = "nazov";
    public static final String SHORT_DESC  = "kratky_popis";
    public static final String LONG_DESC   = "dlhy_popis";
    public static final String DATE        = "datum";
    public static final String CITY_ID     = "mesto_id";
    public static final String INTEREST_ID = "bod_zaujmu_id";
    public static final String EMPLOYEE_ID = "zamestnanec_id";
    
    
    private static Table_Akcia im = null;
    private Connection con;
    private Statement stmt = null;

    public static Table_Akcia getInstance(){
        if(im == null){
            im = new Table_Akcia();
        }
        return im;
    }
    //=======================metody-------------------------
    private void databaseConnect() throws SQLException{
        DatabaseConnector dc = DatabaseConnector.getInstance();
        con = dc.getConnection();
        stmt = con.createStatement();
    }

        //--------------------------------------------------------------------------
    /**
     * vrati vsetky body zaujmu podla podmienky s prvym obrazkom
     * @return ArrayList<Akcia>
     */
    public ArrayList<Akcia> getCondAkcie(String podmienka) throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<Akcia> score = new ArrayList<Akcia>();
        try {
            databaseConnect();
            rs = stmt.executeQuery("" +
                    "SELECT a.id, a.nazov, a.kratky_popis, a.dlhy_popis, a.datum, b.meno, c.nazov, d.login " +
                    "FROM akcia a " +
                    "LEFT JOIN (mesto b, bod_zaujmu c, zamestnanec d" +
                    ") ON (" +
                    "a.bod_zaujmu_id = c.id AND a.mesto_id = c.id AND a.zamestnanec_id = d.id ) " +
                    "WHERE 1 " + podmienka +
                    " GROUP BY a.id ORDER BY a.nazov"
            );
            
            while(rs.next()){
                String id = rs.getString(1);
                String nazov = rs.getString(2);
                String kratky_popis = rs.getString(3);
                String dlhy_popis = rs.getString(4);
                String datum = rs.getString(5);
                String mesto_meno = rs.getString(6);
                String bod_nazov = rs.getString(7);
                String zamestnanec = rs.getString(8);

                ResultSet rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.cesta " +
                        "FROM obrazky a " +
                        "LEFT JOIN (obrazok_akcia b, bod_zaujmu c) ON " +
                        "(a.id = b.obrazky_id AND b.akcia_id = c.id)" +
                        "WHERE c.id = " + id
                );
                Obrazok obrazky = new Obrazok();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    obrazky = new Obrazok(o_id, o_cesta);
                }
                Akcia temp = new Akcia(id, nazov, kratky_popis, dlhy_popis, datum, mesto_meno, bod_nazov, zamestnanec, obrazky);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_Akcia.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }

        }
        return score;
    }

    
    /**
     * vloží akciu predanú ako parameter do databázy, ak vráti hodnotu false, 
     * tak bola databázová transakcia neúspešná, ak true, tak bola úspešná
     * @return boolean
     */
    
    public boolean insertAkcia(Akcia vkladane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
            String s = "INSERT INTO " + TABLE +
                    "(" + NAME + "," + SHORT_DESC + "," + LONG_DESC + "," + DATE + "," + CITY_ID + "," + INTEREST_ID + "," + EMPLOYEE_ID + ")" +
                    "VALUES ('" + vkladane.getNazov() + "','" + vkladane.getKratkyPopis() + "','"
                    + vkladane.getDlhyPopis() + "','" + vkladane.getDatum() + "','" + vkladane.getMesto() + "','" + vkladane.getBodZaujmu() + "','" + vkladane.getZamestnanec() + "')";

            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            String s = ex.getMessage();
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    /**
     * zmaže akciu špecifikovanú id v parametre, ak vráti hodnotu false, 
     * tak bola databázová transakcia neúspešná, ak true, tak bola úspešná
     * @return boolean
     */    

    public boolean deleteAkcia(int id) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
            String s = "DELETE FROM " + TABLE + " WHERE " + ID + " = " + id;
            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            String s = ex.getMessage();
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    /**
     * aktualizuje akciu predanú ako parameter v databáze, ak vráti hodnotu false, 
     * tak bola databázová transakcia neúspešná, ak true, tak bola úspešná
     * @return boolean
     */
    

    public boolean updateAkcia(Akcia upravovane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
            String s = "UPDATE " + TABLE + " SET " + NAME + " = '" + upravovane.getNazov() + "', " + 
                    SHORT_DESC + " = '" + upravovane.getKratkyPopis() + "', " + 
                    LONG_DESC + " = '" + upravovane.getDlhyPopis() + "', " + 
                    DATE + " = '" + upravovane.getDatum() + "', " + 
                    CITY_ID + " = '" + upravovane.getDatum() + "', " + 
                    INTEREST_ID + " = '" + upravovane.getBodZaujmu() + "', " + 
                    EMPLOYEE_ID + " = '" + upravovane.getZamestnanec() + "'";
                        
            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            String s = ex.getMessage();
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    

}
